CREATE DATABASE PaperPresentation
go

USE PaperPresentation
go

CREATE TABLE Category
(
	CategoryId INT IDENTITY(1,1) PRIMARY KEY,
	CategoryName NVARCHAR(100),
	isDelete bit default(1)
)
go

CREATE TABLE [Event]
(
	EventID int identity(1,1) primary key,
	CategoryId int references Category(CategoryId),
	EventName nvarchar(256),
	StartDate datetime,
	EndDate datetime,
	[Address] nvarchar(400),
	Images varchar(256),
	[Description] nvarchar(1000),
	Price float,
	NameWin int references Register(RegisterId),
	CountView int default(0),
	isDelete bit 
)
go

CREATE TABLE Register
(
	RegisterId int identity(1,1) primary key,
	RegisterName nvarchar(50),
	Email nvarchar(50),
	UserName varchar(50),
	[Password] varchar(50),
	MoneyNumber float,
	Phone varchar(20),
	Gender varchar(15),
	Images varchar(256),
	[Address] varchar(256),
	DateCreate datetime,
	[Status] INT,
	isDelete bit default(1)
)
go

CREATE TABLE EnrollEvent
(
	EnrollEventID int identity(1,1) primary key,
	EventID int references [Event](EventID),
	RegisterId int references Register(RegisterId),
	[Status] INT,
	PayMoney float,
	isDelete bit default(1)
)
go

CREATE TABLE [User]
(
	UserId int identity(1,1) primary key,
	UserName varchar(50),
	[Password] varchar(50)
)
go

--------------------------------------------------------------
Create proc sp_Category_ViewAll
AS
Select * from Category
where isDelete = 0
go

Create Proc sp_Category_Insert
(
	@CategoryName NVARCHAR(100)
)
AS
insert into Category(CategoryName) values (@CategoryName)
go

Create proc sp_Category_Update
(
	@CategoryName NVARCHAR(100)
)
AS
update Category set CategoryName = @CategoryName
go

Create proc sp_Category_Delete
AS
update Category set isDelete = 1
go
------------------------------------------------------

Create proc sp_Event_ViewAll
AS
Select * from [Event]
where isDelete = 0
go

Create proc sp_Event_Insert
(
	@CategoryId int,
	@EventName nvarchar(256),
	@StartDate datetime,
	@EndDate datetime,
	@Address nvarchar(400),
	@Images nvarchar(256),
	@Description nvarchar(1000),
	@Price float
)
AS
Insert into [Event](CategoryId,EventName,StartDate,EndDate,[Address],Images,[Description],Price)
values(@CategoryId,@EventName,@StartDate,@EndDate,@Address,@Images,@Description,@Price)
go

Create proc sp_Event_Update
(
	@EventID int,
	@CategoryId int,
	@EventName nvarchar(256),
	@EndDate datetime,
	@Address nvarchar(400),
	@Images nvarchar(256),
	@Description nvarchar(1000),
	@Price float
)
AS
Update [Event] set CategoryId = @CategoryId,
					EventName = @EventName,
					EndDate = @EndDate,
					[Address] = @Address,
					Images = @Images,
					[Description] = @Description,
					Price = @Price
Where EventID = @EventID
go

Create proc sp_Event_Delete
AS
Update [Event] set isDelete = 1
go

------------------------------------------------------
Create proc sp_Register_Viewall
AS
Select * from [Register]
where isDelete = 0
go

Create proc sp_Register_Insert
(
	@RegisterId int ,
	@RegisterName nvarchar(50),
	@Email nvarchar(50),
	@UserName varchar(50),
	@Password varchar(50),
	@MoneyNumber float,
	@Phone varchar(20),
	@Gender varchar(15),
	@Images varchar(256),
	@Address varchar(256),
	@DateCreate datetime,
	@Status INT
)
AS
Insert into [Register](RegisterId,RegisterName,Email,UserName,[Password],MoneyNumber,Phone,Gender,Images,[Address],DateCreate,[Status])
values(@RegisterId,@RegisterName,@Email,@UserName,@Password,@MoneyNumber,@Phone,@Gender,@Images,@Address,@DateCreate,@Status)
go

Create proc sp_Register_Update
(
	@RegisterId int ,
	@RegisterName nvarchar(50),
	@Email nvarchar(50),
	@UserName varchar(50),
	@Password varchar(50),
	@MoneyNumber float,
	@Phone varchar(20),
	@Gender varchar(15),
	@Images varchar(256),
	@Address varchar(256),
	@DateCreate datetime,
	@Status INT
)
AS
Update [Register] set   RegisterName = @RegisterName,
						Email = @Email,
						UserName = @UserName,
						[Password] = @Password,
						MoneyNumber = @MoneyNumber,
						Phone = @Phone,
						Gender = @Gender,
						Images = @Images,
						[Address] = @Address,
						DateCreate = @DateCreate,
						[Status] = @Status
Where RegisterId = @RegisterId
go

Create proc sp_Register_Delete
AS
Update [Register] set isDelete = 1
go
------------EnrollEvent-------------
 
Create proc sp_EnrollEvent_ViewAll
AS
Select * from [EnrollEvent]
where isDelete = 0
go

Create proc sp_EnrollEvent_Insert
(
	@EventID int,
	@RegisterId int,
	@Status int,
	@PayMoney float
)
AS
Insert into [EnrollEvent](EventID,RegisterId,Status,PayMoney)
values(@EventID,@RegisterId,@Status,@PayMoney)
go

Create proc sp_EnrollEvent_Update
(
	@EnrollEventID int,
	@EventID int,
	@RegisterId int,
	@Status int,
	@PayMoney float
)
AS
Update [EnrollEvent] set EventID = @EventID,
					RegisterId = @RegisterId,
					Status = @Status,
					PayMoney = @PayMoney
Where EnrollEventID = @EnrollEventID 
go

Create proc sp_EnrollEvent_Delete
AS
Update [EnrollEvent] set isDelete = 1
go
------------User-------------
Create proc sp_User_Insert
(
	@UserName varchar(50),
	@Password varchar(50)
)
AS
Insert into [User](UserName,[Password])
values(@UserName,@Password)
go

Create proc sp_User_Update
(
	@UserID int,
	@UserName varchar(50),
	@Password varchar(50)
)
AS
Update [User] set UserName = @UserName,
				  [PassWord] = @Password
Where UserID = @UserID
go
------------------





